using System;
using System.Data;
using Mono.Data.SqliteClient;

namespace kedhlow
{
	public class KDB
	{
		
		IDbConnection dbconn;
        IDbCommand dbcmd;
		Util util = new Util();
		
		public KDB (string DBPath)
		{
			string DBConnectionString = "URI=file:" + DBPath + ",version=3";
            try
            {
				dbconn = (IDbConnection) new SqliteConnection(DBConnectionString);
				dbconn.Open();
				dbcmd = dbconn.CreateCommand();
            }
            catch(Exception ex)
			{
				throw ex;
            }
		}
		
		//Create initial database
		public bool CreateInitialDatabase()
		{
			try
			{
				string[] DBTableCreateCommandArray = new string[]
				{
					"CREATE TABLE tblCollections (id INTEGER primary key, name VARCHAR(100));",
					"CREATE TABLE tblItems(id INTEGER primary key, collid INTEGER, title VARCHAR(100), ItemData BLOB, ItemText TEXT, created DATETIME, edited DATETIME, viewcount INTEGER, Starred INTEGER, DataLength INTEGER);",
					"CREATE TABLE tblStars(id INTEGER);"
				};
				
				for(int i = 0; i<DBTableCreateCommandArray.Length; i++)
				{
					dbcmd.CommandText = DBTableCreateCommandArray[i];
					dbcmd.ExecuteNonQuery();
				}
				return true;
			}
			catch(Exception ex)
			{
				Console.WriteLine("Exception while creating database tables:\n {0}\n{1}",ex.Message,ex.StackTrace);
				return false;
			}
		}
		
		//Collections
		public Gtk.ListStore AllCollections()
		{
			Gtk.ListStore ls = new Gtk.ListStore(typeof(int),typeof(string));
			string Query = "SELECT * FROM tblCollections ORDER BY name ASC;";
			dbcmd.CommandText = Query;
			IDataReader reader = dbcmd.ExecuteReader();
       		while(reader.Read())
			{
				ls.AppendValues(reader.GetInt32(0),reader.GetString(1));
			}
       		return ls;
		}
		public string GetCollectionNameByID(int id)
		{
			string Query = "SELECT name FROM tblCollections WHERE id='"+id+"' LIMIT 1;";
			dbcmd.CommandText = Query;
			IDataReader reader = dbcmd.ExecuteReader();
			reader.NextResult();
       		return reader.GetString(0);
		}
		public int AddCollection(string Title)
		{
			string Query;
			Query = "INSERT INTO tblCollections ('name') VALUES ('" + Title + "');";
			dbcmd.CommandText = Query;
			dbcmd.ExecuteNonQuery();
			Query="SELECT last_insert_rowid();";
			dbcmd.CommandText = Query;
			return Convert.ToInt32(dbcmd.ExecuteScalar().ToString());
		}
		public void EditCollectionByID(int id, string Title)
		{
			string Query;
			Query = "UPDATE tblCollections SET 'name'='" + Title + "' WHERE id='"+id+"';";
			dbcmd.CommandText = Query;
			dbcmd.ExecuteNonQuery();
		}
		public void DeleteCollectionById(int id)
		{
			string Query;
			Query = "DELETE FROM tblItems WHERE collid='"+id+"';";
			dbcmd.CommandText = Query;
			dbcmd.ExecuteNonQuery();
			Query = "DELETE FROM tblCollections WHERE id='"+id+"';";
			dbcmd.CommandText = Query;
			dbcmd.ExecuteNonQuery();
		}
		
		//Items
		public Gtk.ListStore AllItemsInCollection(int CollectionID)
		{
			string Query = "SELECT id,collid,title,created FROM tblItems WHERE collid='" + CollectionID + "' ORDER BY title ASC;";
			dbcmd.CommandText = Query;
			IDataReader reader = dbcmd.ExecuteReader();
       		return GetItemsListStore(reader);
		}
		protected Gtk.ListStore GetItemsListStore(IDataReader reader)
		{
			Gtk.ListStore ls = new Gtk.ListStore(typeof(int),typeof(int),typeof(string), typeof(string));
			while(reader.Read())
			{
				ls.AppendValues(reader.GetInt32(0),reader.GetInt32(1),reader.GetString(2),reader.GetString(3));
			}
			return ls;
		}	
		public int AddItem(int CollectionID, string Title, byte[] ItemData, string ItemText, string DateTimeString)
		{
			string dataString = Convert.ToBase64String(ItemData);
			string Query = "INSERT INTO tblItems (collid,title,ItemData,ItemText,created,edited, viewcount, DataLength) " +
				"VALUES ('" + CollectionID + "', '"+util.ReplaceQuotes(Title)+"', '"+dataString+"','"+util.ReplaceQuotes(ItemText)+"','"+DateTimeString+"','"+DateTimeString+"','0','"+ItemData.Length+"');";
			
			dbcmd.CommandText = Query;
			dbcmd.ExecuteNonQuery();
			Query="SELECT last_insert_rowid();";
			dbcmd.CommandText = Query;
			return Convert.ToInt32(dbcmd.ExecuteScalar().ToString());
		}
		public string GetItemTitle(int ItemID)
		{
			string Query = "SELECT Title FROM tblItems WHERE id='"+ItemID+"' LIMIT 1;";
			dbcmd.CommandText = Query;
			IDataReader reader = dbcmd.ExecuteReader();
       		if(reader.Read())
			{
				return reader.GetString(0);
			}
			else { return ""; }
		}
		public byte[] GetItemData(int ItemID)
		{
			string Query = "SELECT ItemData FROM tblItems WHERE id='"+ItemID+"' LIMIT 1;";
			dbcmd.CommandText = Query;
			IDataReader reader = dbcmd.ExecuteReader();
       		if(reader.Read())
			{
				return Convert.FromBase64String(reader.GetString(0));
			}
			else
			{return null;}		
		}
		public string GetItemPlainText(int ItemID)
		{
			string Query = "SELECT ItemText FROM tblItems WHERE id='"+ItemID+"' LIMIT 1;";
			dbcmd.CommandText = Query;
			IDataReader reader = dbcmd.ExecuteReader();
       		if(reader.Read())
			{
				return reader.GetString(0);
			}
			else
			{return null;}		
		}
		public void EditItem(int ItemID, string Title, byte[] ItemData, string ItemText, string DateTimeString)
		{
			string dataString = Convert.ToBase64String(ItemData);
			string Query;
			Query = "UPDATE tblItems SET title='"+util.ReplaceQuotes(Title)+"', ItemData='"+dataString+"', ItemText='"+util.ReplaceQuotes(ItemText)+"', edited='"+DateTimeString+"' WHERE id='"+ItemID+"';";
			dbcmd.CommandText = Query;
			dbcmd.ExecuteNonQuery();
		}
		public void ItemViewCountIncr(int ItemID)
		{
			string Query;
			Query = "UPDATE tblItems SET viewcount = viewcount + 1 WHERE id='"+ItemID+"';";
			dbcmd.CommandText = Query;
			dbcmd.ExecuteNonQuery();
		}
		public void DeleteItem(int ItemID)
		{
			string Query = "DELETE FROM tblItems WHERE id='"+ItemID+"';";
			dbcmd.CommandText = Query;
			dbcmd.ExecuteNonQuery();
		}
		public bool IsItemStarred(int ItemID)
		{
			string Query = "SELECT id FROM tblStars WHERE id='"+ItemID+"' LIMIT 1;";
			dbcmd.CommandText = Query;
			IDataReader reader = dbcmd.ExecuteReader();
       		if(reader.Read()) { return true; }
			else { return false;  }
		}
		public Gtk.ListStore ItemMetaData(int ItemID)
		{
			string Query = "SELECT id,collid,title,ItemData,ItemText,created,edited,viewcount,DataLength FROM tblItems WHERE id='"+ItemID+"' LIMIT 1;";
			dbcmd.CommandText = Query;
			IDataReader reader = dbcmd.ExecuteReader();
			
			//tblItems schema:
			//id INTEGER primary key,
			//collid INTEGER,
			//title VARCHAR(100),
			//ItemData BLOB,
			//ItemText TEXT,
			//created DATETIME,
			//edited DATETIME,
			//viewcount INTEGER
			//DataLength INTEGER)
			
			Gtk.ListStore ls = new Gtk.ListStore(typeof(string), typeof(string));
			while(reader.Read())
			{
				//Generate item path string:
				string ItemPath = "/";
				ItemPath = ItemPath+GetCollectionNameByID(reader.GetInt32(1));
				ItemPath = ItemPath + "/" + reader.GetString(2);
				
				ls.AppendValues("Item path",ItemPath);
				ls.AppendValues("Created",reader.GetString(5));
				ls.AppendValues("Last updated",reader.GetString(6));
				ls.AppendValues("View count",reader.GetInt32(7).ToString());
				ls.AppendValues("Characters",util.StripSpaces(reader.GetString(4)).Length.ToString());
				ls.AppendValues("Bytes",reader.GetInt32(8).ToString());
				if(IsItemStarred(ItemID))
				{ ls.AppendValues("Starred","Yes"); }
				else
				{ ls.AppendValues("Starred","No"); }
			}
			return ls;
		}
		
		//Quick search
		public IDataReader SearchAllCollections(string SearchString)
		{
			IDataReader retRdr = SearchAllCollections(SearchString, true);
			return retRdr;
		}
		public IDataReader SearchAllCollections(string SearchString, bool RequireAll)
		{
			SearchString = util.ReplaceQuotes(SearchString);
			string[] SearchStrings = SearchString.Split(new Char[] {' '});
			string Query = "SELECT id,collid,title,created FROM tblItems WHERE ";
			string WhereQuery = "";
			for (int i = 0; i < SearchStrings.Length; i++)
			{
				WhereQuery = WhereQuery + "ItemText LIKE '%"+SearchStrings[i]+"%'";
				if(i<(SearchStrings.Length-1))
				{
					if(RequireAll)
					{
						WhereQuery = WhereQuery + " AND ";
					}
					else
					{
						WhereQuery = WhereQuery + " OR ";
					}
				}				
			}
			Query = Query + WhereQuery;
			Query = Query + " ORDER BY title ASC;";
			IDataReader retRdr = PerformSearchAllCollections(Query);
			return retRdr;
		}
		public IDataReader PerformSearchAllCollections(string Query)
		{
			
			dbcmd.CommandText = Query;
			IDataReader reader = dbcmd.ExecuteReader();
			return reader;
		}
	
		//Most viewed items
		public Gtk.ListStore MostViewedItems()
		{
			string Query = "SELECT id,title FROM tblItems ORDER BY viewcount DESC LIMIT 10;";
			dbcmd.CommandText = Query;
			IDataReader reader = dbcmd.ExecuteReader();
       		Gtk.ListStore ls = new Gtk.ListStore(typeof(int), typeof(string));
			int iC = 1;
			while(reader.Read())
			{
				ls.AppendValues(reader.GetInt32(0),iC + ". " + reader.GetString(1));
				iC++;
			}
			return ls;
		}

		//Starred items
		public Gtk.ListStore AllStarredItems()
		{
			Gtk.ListStore ls = new Gtk.ListStore(typeof(int), typeof(string));
			
			string Query = "SELECT id FROM tblStars LIMIT 10;";
			dbcmd.CommandText = Query;
			IDataReader reader = dbcmd.ExecuteReader();

			while(reader.Read())
			{
				ls.AppendValues(reader.GetInt32(0),GetItemTitle(reader.GetInt32(0)));
			}
			//ls.ChangeSortColumn(1);
			return ls;
		}
		public void UnstarItem(int ItemID)
		{
			string Query;
			Query = "DELETE FROM tblStars WHERE id LIKE '"+ItemID+"';";
			dbcmd.CommandText = Query;
			dbcmd.ExecuteNonQuery();
		}
		public void StarItem(int ItemID)
		{
			string Query;
			Query = "INSERT INTO tblStars (id) VALUES ('"+ItemID+"');";
			dbcmd.CommandText = Query;
			dbcmd.ExecuteNonQuery();
		}
	}
}